package cn.com.chec.en.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.chec.en.model.domain.WebsiteInfo;

@Repository
public class WebsiteInfoDao {
	protected static final Logger logger = Logger
			.getLogger(WebsiteInfoDao.class);

	public boolean insert(WebsiteInfo websiteInfo) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "insert into tbl_website_c (website_name,website_desc,website_url,website_sign,website_isself) values(?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			pstmt.setString(++i, websiteInfo.getName());
			pstmt.setString(++i, websiteInfo.getDesc());
			pstmt.setString(++i, websiteInfo.getUrl());
			pstmt.setString(++i, websiteInfo.getSign());
			pstmt.setInt(++i, websiteInfo.getIsSelf());
			pstmt.execute();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<WebsiteInfo> getSubList(String kw, int start, int num) {
		List<WebsiteInfo> list = new ArrayList<WebsiteInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_website_c  ";
			if (StringUtils.isNotBlank(kw)) {
				sql = sql
						+ " where (website_name like ? or website_desc like ?) ";
			}
			sql = sql + " limit ? , ?";
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (StringUtils.isNotBlank(kw)) {
				pstmt.setString(++i, "%" + kw + "%");
				pstmt.setString(++i, "%" + kw + "%");
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			ResultSet rs = pstmt.executeQuery();
			list = getListFromRS(rs);
			pstmt.close();
			conn.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	public int getCount(String kw) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_website_c  ";
			if (StringUtils.isNotBlank(kw)) {
				sql = sql
						+ " where (website_name like ? or website_desc like ?) ";
			}
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if (StringUtils.isNotBlank(kw)) {
				pstmt.setString(++i, "%" + kw + "%");
				pstmt.setString(++i, "%" + kw + "%");
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("total");
			}
			pstmt.close();
			conn.close();
			return count;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public WebsiteInfo getWebsite(int id) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_website_c where website_id = ? ";
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(++i, id);
			ResultSet rs = pstmt.executeQuery();
			List<WebsiteInfo> list = getListFromRS(rs);
			pstmt.close();
			conn.close();
			return (list != null) && (list.size() > 0) ? (WebsiteInfo) list
					.get(0) : new WebsiteInfo();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new WebsiteInfo();
	}

	public boolean update(WebsiteInfo website) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "update tbl_website_c set website_name = ? , website_desc = ? , website_url = ? , website_sign = ?, website_isself=? where website_id = ? ";
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(++i, website.getName());
			pstmt.setString(++i, website.getDesc());
			pstmt.setString(++i, website.getUrl());
			pstmt.setString(++i, website.getSign());
			pstmt.setInt(++i, website.getIsSelf());
			pstmt.setInt(++i, website.getId());
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean del(int id) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "delete from  tbl_website_c where website_id = ? ";
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(++i, id);
			pstmt.executeUpdate();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	private List<WebsiteInfo> getListFromRS(ResultSet rs) {
		List<WebsiteInfo> list = new ArrayList<WebsiteInfo>();
		WebsiteInfo website = null;
		try {
			while (rs.next()) {
				website = new WebsiteInfo();
				website.setId(rs.getInt("website_id"));
				website.setName(rs.getString("website_name"));
				website.setDesc(rs.getString("website_desc"));
				website.setUrl(rs.getString("website_url"));
				website.setSign(rs.getString("website_sign"));
				website.setIsSelf(rs.getInt("website_isself"));
				list.add(website);
			}
			rs.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	public boolean batchInsert(List<WebsiteInfo> websites) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			new BaseDao().truncateTable("tbl_website_c");
			String sql = "insert into tbl_website_c (website_id,website_name,website_desc,website_url,website_sign,website_isself) values(?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			for (WebsiteInfo websiteInfo : websites) {
				int i = 0;
				pstmt.setInt(++i, websiteInfo.getId());
				pstmt.setString(++i, websiteInfo.getName());
				pstmt.setString(++i, websiteInfo.getDesc());
				pstmt.setString(++i, websiteInfo.getUrl());
				pstmt.setString(++i, websiteInfo.getSign());
				pstmt.setInt(++i, websiteInfo.getIsSelf());

				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public WebsiteInfo getWebsiteSelf() {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_website_c where website_isself = 1 limit 0 , 1";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			List<WebsiteInfo> list = getListFromRS(rs);
			pstmt.close();
			conn.close();
			return (list != null) && (list.size() > 0) ? (WebsiteInfo) list
					.get(0) : new WebsiteInfo();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new WebsiteInfo();
	}
}